USE [DSRSystem]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetSourcesForGC]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spGetSourcesForGC]
GO

CREATE PROCEDURE [dbo].[spGetSourcesForGC]
	@Companies NVARCHAR(MAX),
	@Quarter DATETIME
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @ChangedSince DATETIME
	DECLARE @CurrentDate DATETIME = GETUTCDATE()
	DECLARE @years INT = -2

	SET @ChangedSince = DATEADD(YEAR, @years, @CurrentDate)

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @Companies, 1)

	DECLARE @SelectedCompanies TABLE
	(
		CompanyId uniqueidentifier NOT NULL
	)
	INSERT INTO @SelectedCompanies
	SELECT TU.id.value('.','uniqueidentifier') AS CompanyId
	FROM @XML.nodes('/Companies/Company') AS T(ei) 
		outer apply T.ei.nodes('Id') as TU(id) 


	DECLARE @sources TABLE
	(
		CompanyId UNIQUEIDENTIFIER NOT NULL,
		SourceType INT NOT NULL,
		SourceText NVARCHAR(500) NULL,
		LastUpdate DATETIME NOT NULL
	)

	INSERT INTO @sources

	SELECT	Companies.CompanyId,
			dbo.fnGetSourceType(dbo.Sources.Name) AS SourceType,
			CAST(dbo.Sources.Text AS nvarchar(500)) AS SourceText,
			dbo.Sources.LastUpdate
	FROM    dbo.Companies INNER JOIN
			@SelectedCompanies AS SelectedCompanies ON  SelectedCompanies.CompanyId = Companies.CompanyId INNER JOIN
			dbo.IndicatorsInCompanies ON dbo.IndicatorsInCompanies.CompanyId = Companies.CompanyId INNER JOIN
			dbo.Indicators ON dbo.IndicatorsInCompanies.IndicatorId = dbo.Indicators.IndicatorId AND dbo.Companies.WorkingVersion - 1 = dbo.Indicators.Version INNER JOIN 
			dbo.IndicatorTopics ON dbo.Indicators.TopicId = dbo.IndicatorTopics.TopicId INNER JOIN
			dbo.IndicatorThemes ON dbo.IndicatorTopics.ThemeId = dbo.IndicatorThemes.ThemeId INNER JOIN
            dbo.IndicatorCategories ON dbo.Indicators.CategoryId = dbo.IndicatorCategories.IndicatorCategoryId INNER JOIN
			dbo.IndicatorSources ON dbo.IndicatorSources.IndicatorId = dbo.Indicators.IndicatorId INNER JOIN
			dbo.Sources ON dbo.IndicatorSources.SourceId = dbo.Sources.SourceId AND dbo.Sources.CompanyId = Companies.CompanyId
	WHERE   (dbo.Indicators.Enabled = 1) AND
			(dbo.IndicatorThemes.Number > 1) AND 
			(dbo.IndicatorCategories.[Order] < 4) AND
			(dbo.Sources.LastUpdate >= @ChangedSince)
	UNION
	SELECT	Companies.CompanyId,
			dbo.fnGetSourceType(dbo.Sources.Name) AS SourceType,
			CAST(dbo.Sources.Text AS nvarchar(500)) AS SourceText,
			dbo.Sources.LastUpdate
	FROM	dbo.Companies INNER JOIN
			@SelectedCompanies AS SelectedCompanies ON  SelectedCompanies.CompanyId = Companies.CompanyId INNER JOIN
			dbo.IndicatorsInCompanies ON dbo.IndicatorsInCompanies.CompanyId = Companies.CompanyId INNER JOIN
			dbo.Indicators ON dbo.IndicatorsInCompanies.IndicatorId = dbo.Indicators.IndicatorId AND dbo.Companies.WorkingVersion - 1 = dbo.Indicators.Version INNER JOIN
			dbo.IndicatorTopics ON dbo.Indicators.TopicId = dbo.IndicatorTopics.TopicId INNER JOIN
			dbo.IndicatorThemes ON dbo.IndicatorTopics.ThemeId = dbo.IndicatorThemes.ThemeId INNER JOIN
			dbo.IndicatorCategories ON dbo.Indicators.CategoryId = dbo.IndicatorCategories.IndicatorCategoryId INNER JOIN
			dbo.CommentsAlphanumeric ON dbo.CommentsAlphanumeric.IndicatorId = dbo.Indicators.IndicatorId INNER JOIN
			dbo.CommentsAlphanumericSources ON dbo.CommentsAlphanumeric.CommentsAlphanumericId = dbo.CommentsAlphanumericSources.CommentsAlphanumericId INNER JOIN
			dbo.Sources ON dbo.CommentsAlphanumericSources.SourceId = dbo.Sources.SourceId AND dbo.Sources.CompanyId = Companies.CompanyId
	WHERE	(dbo.Indicators.Enabled = 1) AND
			(dbo.IndicatorThemes.Number > 1) AND 
			(dbo.IndicatorCategories.[Order] < 4)AND
			(dbo.Sources.LastUpdate >= @ChangedSince)

	SELECT	CompanyId,
	        @Quarter AS Quater,
			1 AS Type,
			0 AS EventIndicatorAssessment,
			@CurrentDate AS PublicationDate,
			SourceText AS NewsArticleName,
			'' AS SourceName
	FROM @sources
	WHERE (SourceType = 1)
	GROUP BY CompanyId, SourceText
	ORDER BY CompanyId, SourceText

END
GO


